package uit.shoeshop.dao;
import uit.shoeshop.dto.ProductDTO;
import uit.shoeshop.lib.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;


public class ProductDAO {
	private Connection conn;
	private String tableName;
	
	public ProductDAO(){
		setConn(null);
		setTableName(Constants.TBL_PRODUCT);
	}

	public ArrayList<ProductDTO> getFeaturedProduct() throws SQLException{
		ArrayList<ProductDTO> results = new ArrayList<ProductDTO>();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableName() + " WHERE enabled = 1 ORDER BY create_date DESC LIMIT 0,"+Constants.LIMIT_FEATHER;
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				ProductDTO product = new ProductDTO();
				product.setProduct_id(rs.getInt("product_id"));
				product.setEnabled(rs.getInt("enabled"));
				product.setTitle(rs.getString("title"));
				product.setImage_main(rs.getString("image_main"));
				product.setPrice(rs.getInt("price"));
				results.add(product);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public ArrayList<ProductDTO> getProductbyCateID(int cid, int page) throws SQLException{
		ArrayList<ProductDTO> results = new ArrayList<ProductDTO>();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableName() + " WHERE enabled = 1 AND category_id = ? ORDER BY create_date DESC LIMIT ? , ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, cid);
			stm.setInt(2, Constants.LIMIT_CATEGORY*(page-1));
			stm.setInt(3, Constants.LIMIT_CATEGORY);
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				ProductDTO product = new ProductDTO();
				product.setProduct_id(rs.getInt("product_id"));
				product.setEnabled(rs.getInt("enabled"));
				product.setTitle(rs.getString("title"));
				product.setImage_main(rs.getString("image_main"));
				product.setPrice(rs.getInt("price"));
				results.add(product);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public Integer countProductbyCateID(int cid) throws SQLException{
		Integer results = 0;
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT count(*) as total_product FROM " + getTableName() + " WHERE enabled = 1 AND category_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, cid);
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				results = rs.getInt("total_product");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public ArrayList<ProductDTO> getProductbyKeyword(String keyword,int page) throws SQLException{
		ArrayList<ProductDTO> results = new ArrayList<ProductDTO>();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableName() + " WHERE enabled = 1 AND title LIKE '%"+keyword+"%' ORDER BY create_date DESC LIMIT ? , ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, Constants.LIMIT_CATEGORY*(page-1));
			stm.setInt(2, Constants.LIMIT_CATEGORY);
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				ProductDTO product = new ProductDTO();
				product.setProduct_id(rs.getInt("product_id"));
				product.setEnabled(rs.getInt("enabled"));
				product.setTitle(rs.getString("title"));
				product.setImage_main(rs.getString("image_main"));
				product.setPrice(rs.getInt("price"));
				results.add(product);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public Integer countProductbyKeyword(String keyword) throws SQLException{
		Integer results = 0;
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT count(*) as total_product FROM " + getTableName() + " WHERE enabled = 1 AND title LIKE '%"+keyword+"%'";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				results = rs.getInt("total_product");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public ProductDTO getProductDetail(int productId) throws SQLException{
		ProductDTO result = new ProductDTO();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableName() + " WHERE enabled = 1 AND product_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, productId);
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				result.setProduct_id(rs.getInt("product_id"));
				result.setCategory_id(rs.getInt("category_id"));
				result.setEnabled(rs.getInt("enabled"));
				result.setTitle(rs.getString("title"));
				result.setImage_main(rs.getString("image_main"));
				result.setImage_1(rs.getString("image_1"));
				result.setImage_2(rs.getString("image_2"));
				result.setImage_3(rs.getString("image_3"));
				result.setPrice(rs.getInt("price"));
				result.setIn_stock(rs.getInt("in_stock"));
				result.setDescription(rs.getString("description"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return result;
	}
	public ProductDTO getProductDetailRaw(int productId) throws SQLException{
		ProductDTO result = new ProductDTO();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableName() + " WHERE product_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, productId);
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				result.setProduct_id(rs.getInt("product_id"));
				result.setCategory_id(rs.getInt("category_id"));
				result.setEnabled(rs.getInt("enabled"));
				result.setTitle(rs.getString("title"));
				result.setImage_main(rs.getString("image_main"));
				result.setImage_1(rs.getString("image_1"));
				result.setImage_2(rs.getString("image_2"));
				result.setImage_3(rs.getString("image_3"));
				result.setPrice(rs.getInt("price"));
				result.setIn_stock(rs.getInt("in_stock"));
				result.setDescription(rs.getString("description"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return result;
	}	
	public ArrayList<ProductDTO> getAllProduct(int page) throws SQLException{
		ArrayList<ProductDTO> results = new ArrayList<ProductDTO>();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableName() + " ORDER BY create_date DESC LIMIT "+ (page-1)*Constants.LIMIT_PRODUCT +","+Constants.LIMIT_PRODUCT;
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				ProductDTO product = new ProductDTO();
				product.setProduct_id(rs.getInt("product_id"));
				product.setCategory_id(rs.getInt("category_id"));
				product.setIn_stock(rs.getInt("in_stock"));
				product.setEnabled(rs.getInt("enabled"));
				product.setTitle(rs.getString("title"));
				product.setImage_main(rs.getString("image_main"));
				product.setPrice(rs.getInt("price"));
				results.add(product);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public Integer countAllProduct() throws SQLException{
		Integer results = 0;
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT count(*) as total_product FROM " + getTableName();
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				results = rs.getInt("total_product");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public int delProduct(int pId) throws SQLException{
		int result = 1;
		try{
			setConn(Util.Connect());
			String sql = "DELETE FROM " +getTableName() +" WHERE product_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			stm.setInt(1, pId);
			result = stm.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			result = 0;
		}finally{
			if(getConn() != null)
				getConn().close();
		}		
		return result;
	}
	
	public int updateActive(int pId,int enable) throws SQLException{
		int result = 1;
		try{
			setConn(Util.Connect());
			String sql = "";
			sql = "UPDATE "+getTableName()+" SET enabled = ? WHERE product_id = ?";			

			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, enable);
			stm.setInt(2, pId);
			
			if(stm.executeUpdate() < 1)
				throw new Exception("Khong update enabled cho product duoc !");
		}catch(Exception e){
			e.printStackTrace();
			result=0;
		}finally{
			if(getConn() != null)
				getConn().close();
		}		
		return result;
	}
	
	public int updateProduct(int pId, String title, String desc, int price,
			int stock, int cate, int enabled, String image0, String image1,
			String image2, String image3) throws SQLException{
		
		int result = 1;
		try{
			Date currentDate = new Date();
			setConn(Util.Connect());
			String sql = "";
			sql = "UPDATE "+getTableName()+" SET category_id=?,title=?,description=?,price=?,in_stock=?,image_main=?,image_1=?,image_2=?,image_3=?,create_date=?,enabled = ? WHERE product_id = ?";				

			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, cate);
			stm.setString(2, title);
			stm.setString(3, desc);
			stm.setInt(4, price);
			stm.setInt(5, stock);
			stm.setString(6, image0);
			stm.setString(7, image1);
			stm.setString(8, image2);
			stm.setString(9, image3);
			stm.setInt(10, (int)(currentDate.getTime()/ 1000));
			stm.setInt(11, enabled);
			stm.setInt(12, pId);
			if(stm.executeUpdate() < 1)
				throw new Exception("Khong update thong tin cho product duoc !");
		}catch(Exception e){
			e.printStackTrace();
			result=0;
		}finally{
			if(getConn() != null)
				getConn().close();
		}			
		return result;
	}
	
	public int insertProduct(String title, String desc, int price,
			int stock, int cate, int enabled, String image0, String image1,
			String image2, String image3) throws SQLException{
		
		int result = 1;
		try{
			Date currentDate = new Date();
			setConn(Util.Connect());
			String sql = "";
		
			sql = "INSERT INTO "+getTableName()+" (category_id,title,description,price,in_stock,image_main,image_1,image_2,image_3,create_date,enabled) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			//gan gia tri cho cau sql
			stm.setInt(1, cate);
			stm.setString(2, title);
			stm.setString(3, desc);
			stm.setInt(4, price);
			stm.setInt(5, stock);
			stm.setString(6, image0);
			stm.setString(7, image1);
			stm.setString(8, image2);
			stm.setString(9, image3);
			stm.setInt(10, (int)(currentDate.getTime()/ 1000));
			stm.setInt(11, enabled);

			if(stm.executeUpdate() < 1)
				throw new Exception("Khong insert thong tin cho product duoc !");
		}catch(Exception e){
			e.printStackTrace();
			result=0;
		}finally{
			if(getConn() != null)
				getConn().close();
		}			
		return result;
	}	
	/**
	 * @param conn the conn to set
	 */
	public void setConn(Connection conn) {
		this.conn = conn;
	}

	/**
	 * @return the conn
	 */
	public Connection getConn() {
		return conn;
	}

	/**
	 * @param tableName the tableName to set
	 */
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * @return the tableName
	 */
	public String getTableName() {
		return tableName;
	}
}
